package org.qu.userdao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.qu.userdao.IUserDao;
import org.qu.vo.IsnsRegisterTemp;
import org.qu.vo.IsnsUsers;

import com.opensymphony.xwork2.ActionContext;

/**
 * 用户管理实现类
 * @author 韩进城
 *
 */
public class UserManagerImpl implements IUserDao {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	public UserManagerImpl(Connection connection){
		this.conn=connection;
	}
	/**
	 * 关闭连接的方法
	 */
	public void thisclose(){
			try {
				if(rs!=null){
				rs.close();
				}
				if(ps!=null){
					ps.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		
	}
	
	/**
	 * 验证邮箱是否存在
	 * @throws SQLException 
	 */
	public boolean isEmail(String email) throws Exception{
		// TODO Auto-generated method stub
		boolean stat=false;
		if(!"".equals(email)){
			
				ps = conn.prepareStatement("SELECT user_id FROM isns_users WHERE user_email=?");
				
				ps.setString(1, email);
				
				rs=ps.executeQuery();
				
				if(rs.next()){
					stat=true;
				}
				thisclose();
		}
		return stat;
	}
	
	
	public boolean isLogin(IsnsUsers user) throws Exception{
		// TODO Auto-generated method stub
		return false;
	}
	/**
	 * 用户注册添加
	 */
	public boolean register(IsnsUsers user) throws Exception {
		// TODO Auto-generated method stub
		boolean stat=false;
		if(user!=null){
			
			ps=conn.prepareStatement("INSERT INTO isns_users(user_email,user_name,user_pws,user_sex)VALUES (?,?,?,?)");
			
			ps.setString(1, user.getUserEmail());
			
			ps.setString(2, user.getUserName());
			
			ps.setString(3, user.getUserPws());
			
			ps.setInt(4, user.getUserSex());
			
			if(ps.executeUpdate()>0){
				stat=true;
			}
			
			thisclose();
		}
		
		return stat;
	}
	/**
	 * 判断邮箱和密码该用户是否存在
	 */
	public boolean isEmail(String email, String password) throws Exception {
		// TODO Auto-generated method stub
		boolean stat=false;
		
		ps=conn.prepareStatement("SELECT user_id FROM isns_users WHERE user_email=? AND user_pws=?");
		
		ps.setString(1, email);
		
		ps.setString(2, password);
		
		rs=ps.executeQuery();
		
		if(rs.next()){
			stat=true;
		}
		
		thisclose();
		return stat;
		
	}
	
	/**
	 * 信息保存在临时数据表中
	 */
	public boolean doCreateRegisterTemp(IsnsRegisterTemp temp) throws Exception {
		// TODO Auto-generated method stub
		boolean flag=false;
		
		if(temp!=null){
			
			ps=conn.prepareStatement("INSERT INTO isns_register_temp(email,username,userpwd,sex,createdate)VALUES(?,?,?,?,?)");
			
			ps.setString(1, temp.getEmail());
			
			ps.setString(2, temp.getUserName());
			
			ps.setString(3, temp.getUserPwd());
			
			ps.setInt(4, temp.getSex());
			
			ps.setDate(5, new java.sql.Date(temp.getCreateDate().getTime()));
			
			if(ps.executeUpdate()>0){
				
				flag=true;
				
			}
			
			thisclose();
		}
		return flag;
	}
	
	/**
	 * 查询临时表中此人是否存在
	 */
	public IsnsRegisterTemp isRegisterTemp(String userEmail, String userPwd) throws Exception {
		// TODO Auto-generated method stub
		IsnsRegisterTemp temp=null;
		
		if(userEmail!=null && userPwd!=null){
			
			ps=conn.prepareStatement("SELECT * FROM isns_register_temp WHERE email=? AND userpwd=?");
			
			ps.setString(1, userEmail);
			
			ps.setString(2, userPwd);
			
			rs=ps.executeQuery();
			
			if(rs.next()){
				temp=new IsnsRegisterTemp();
				temp.setId(rs.getInt("id"));
				temp.setEmail(rs.getString("email"));
				temp.setUserName(rs.getString("username"));
				temp.setUserPwd(rs.getString("userpwd"));
				temp.setSex(rs.getInt("sex"));
				temp.setCreateDate(rs.getDate("createdate"));
			}
		}
		thisclose();
		
		return temp;
	}
	
	/**
	 * 查询临时表中此人是否存在
	 */
	public IsnsRegisterTemp isRegisterTemp(int id,String email) throws Exception {
		// TODO Auto-generated method stub
		IsnsRegisterTemp temp=null;
		
		if(id>0){
			
			ps=conn.prepareStatement("SELECT * FROM isns_register_temp WHERE id=? AND email=?");
			
			ps.setInt(1, id);
			
			ps.setString(2, email);
			
			rs=ps.executeQuery();
			
			if(rs.next()){
				temp=new IsnsRegisterTemp();
				temp.setId(rs.getInt("id"));
				temp.setEmail(rs.getString("email"));
				temp.setUserName(rs.getString("username"));
				temp.setUserPwd(rs.getString("userpwd"));
				temp.setSex(rs.getInt("sex"));
				temp.setCreateDate(rs.getDate("createdate"));
			}
		}
		thisclose();
		
		return temp;
	}
	
	/**
	 * 删除临时表数据
	 */
	public boolean removeIsRegisterTemp(int id) throws Exception {
		// TODO Auto-generated method stub
		
		boolean flag=false;
		
		if(id>0){
			
			ps=conn.prepareStatement("DELETE FROM isns_register_temp WHERE id=?");
			
			ps.setInt(1, id);
			
			if(ps.executeUpdate()>0){
				
				flag=true;
			}
		}
		thisclose();
		
		return flag;
	}
	/**
	 * 批量删除临时表中的信息
	 */
	public boolean removeIsRegisterTemp(List<Integer> ids) throws Exception {
		// TODO Auto-generated method stub
		boolean flag=false;
		
		if(ids!=null && ids.size()>0){
			
			Iterator<Integer> iterator=ids.iterator();
			
			while(iterator.hasNext()){
				
				this.removeIsRegisterTemp(iterator.next());
				
				flag=true;
			}
			
		}
		return flag;
	}
	/**
	 * 
	 * 王建
	 * 验证临时表邮箱是否存在
	 */
	public boolean isRegisterTempEmail(String email) throws Exception{
		// TODO Auto-generated method stub
		boolean stat=false;
		if(!"".equals(email)){
			
				ps = conn.prepareStatement("SELECT email FROM isns_register_temp WHERE email=?");
				
				ps.setString(1, email);
				
				rs=ps.executeQuery();
				
				if(rs.next()){
					stat=true;
				}
				thisclose();
		}
		return stat;
	}
	/**
	 * 查询超过7天的id集合
	 */
	public List<Integer> getRepeatIds() throws Exception {
		// TODO Auto-generated method stub
		List<Integer> ids=new ArrayList<Integer>();;
		
		ps=conn.prepareStatement("SELECT id FROM isns_register_temp WHERE datediff(sysDate(),createdate)>7");
		
		rs=ps.executeQuery();
		
		while(rs.next()){
			
			ids.add(rs.getInt(1));
		}
		
		return ids;
	}
	
}
